﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace VoteMS.DAL
{
    /*
	 * @author：name
	 * @time：2017/12/28 
	 * 
	 */
    public class VoteRecordDAL
    {
        #region 添加
        /// <summary>
        /// 插入投票记录
        /// </summary>
        /// <param name="macAddress">投票唯一码</param>
        /// <param name="voted">投票记录</param>
        /// <returns></returns>
        public int AddRecord(string macAddress, string voted)
        {
            string strsql="insert into VoteRecords values(@projectsids,@recordtime,@recorder)";
            SqlParameter[] parameters =
            {
                new SqlParameter("@projectsids", SqlDbType.VarChar, 50),
                new SqlParameter("@recordtime", SqlDbType.DateTime),
                new SqlParameter("@recorder", SqlDbType.VarChar, 20)
            };
            parameters[0].Value = voted;
            parameters[1].Value = DateTime.Now;
            parameters[2].Value = macAddress;
            return DBHelper.ExecNoQuery(strsql, parameters);
        }
        #endregion

        #region 修改
        public int UpdateRecord(string macAddress, string voted)
        {
            string strsql =
                "update VoteRecords set projectids=@projectsids where convert (varchar(10) , RecordTime,23)=@recordtime and recorder=@recorder)";
            SqlParameter[] parameters =
            {
                new SqlParameter("@projectsids", SqlDbType.VarChar, 50),
                new SqlParameter("@recordtime", SqlDbType.VarChar,10),
                new SqlParameter("@recorder", SqlDbType.VarChar, 20)
            };
            parameters[0].Value = voted;
            parameters[1].Value = DateTime.Now.ToString("yyyy-MM-dd");
            parameters[2].Value = macAddress;
            return DBHelper.ExecNoQuery(strsql, parameters);
        }

        //插入投票记录
        public int CreateRecord(int pid, string macAddress, string voted)
        {
            SqlParameter[] parameters;
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" begin transaction ");
            if (ExistsRecord(macAddress))
            {
                //如果存在则修改投票记录
                strsql.Append(
                    " update VoteRecords set projectids=@projectsids where convert (varchar(10) , RecordTime,23)=@recordtime and recorder=@recorder ");
                parameters = new SqlParameter[]
                {
                    new SqlParameter("@projectsids", SqlDbType.VarChar, 50),
                    new SqlParameter("@recordtime", SqlDbType.VarChar, 10),
                    new SqlParameter("@recorder", SqlDbType.VarChar, 20),
                    new SqlParameter("@projectid", SqlDbType.Int)
                };
                parameters[0].Value = voted;
                parameters[1].Value = DateTime.Now.ToString("yyyy-MM-dd");
                parameters[2].Value = macAddress;
            }
            else
            {
                //不存在，添加投票记录
                strsql.Append(
                    " insert into VoteRecords values(@projectsids,@recordtime,@recorder) ");
                parameters = new SqlParameter[]
                {
                    new SqlParameter("@projectsids", SqlDbType.VarChar, 50),
                    new SqlParameter("@recordtime", SqlDbType.DateTime),
                    new SqlParameter("@recorder", SqlDbType.VarChar, 20),
                    new SqlParameter("@projectid", SqlDbType.Int)
                };

                parameters[0].Value = voted;
                parameters[1].Value = DateTime.Now;
                parameters[2].Value = macAddress;
            }
            parameters[3].Value = pid;
            strsql.Append(" update Project set projectnumber=projectnumber+1 where projectid=@projectid ")
                .Append(" commit transaction ");
            int num = DBHelper.ExecNoQuery(strsql.ToString(), parameters);
            return num;
        }

        #endregion

        #region 查询

        public bool ExistsRecord(string macAddress)
        {
            if (!string.Equals(GetRecordByMy(macAddress), ""))
            {
                return true;
            }
            return false;
        }
        public string GetRecordByMy(string uid)
        {
            string strsql = "select ProjectIDs from VoteRecords where Recorder='"
                            + uid + "' and convert (varchar(18) , RecordTime,23)='"
                            + DateTime.Now.Date.ToString("yyyy-MM-dd") + "' ";
            object obj = DBHelper.ExecScalar(strsql);
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }
        }

        #endregion
    }
}
